﻿Imports ComponentFactory.Krypton.Toolkit
Imports System.Data.OleDb
Imports System.Runtime.InteropServices

Public Class Clientes
    Inherits KryptonForm

    Private conexao As OleDbConnection
    Private command As OleDbCommand
    Private dataSet As DataSet
    Private dataView As DataView
    Private dataAdapter As OleDbDataAdapter
    Private cmdBuilder As OleDbCommandBuilder
    Private strQueryFisico As String = "SELECT codCliente AS Código" & _
                                        ", nomeCliente AS Nome" & _
                                        ", sexoCliente AS Sexo" & _
                                        ", dataNascCliente AS Nascimento" & _
                                        ", rgCliente AS RG" & _
                                        ", cpfCliente AS CPF" & _
                                        ", enderecoCliente AS Endereço" & _
                                        ", bairroCliente AS Bairro" & _
                                        ", cidadeCliente AS Cidade" & _
                                        ", tblEstado.siglaEstado AS Estado" & _
                                        " FROM tblCliente " & _
                                        " INNER JOIN tblEstado ON tblEstado.codEstado = tblCliente.codEstado" & _
                                        " WHERE tipoCliente = 'fisico'"

    Private strQueryJuridico As String = "SELECT codCliente AS Código" & _
                                        ", nomeCliente AS Nome" & _
                                        ", cnpjCliente AS CNPJ" & _
                                        ", enderecoCliente AS Endereço" & _
                                        ", bairroCliente AS Bairro" & _
                                        ", tblEstado.siglaEstado AS Estado" & _
                                        ", tblRamoAtividade.descricaoRamoAtividade AS Ramo" & _
                                        " FROM ((tblCliente" & _
                                        " INNER JOIN tblEstado ON tblEstado.codEstado = tblCliente.codEstado)" & _
                                        " INNER JOIN tblRamoAtividade ON tblRamoAtividade.codRamoAtividade = tblCliente.codRamoAtividade)" & _
                                        " WHERE tipoCliente = 'juridico'"
    Private strTipoFiltro = Nothing

    Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.

        strTipoFiltro = "Nome like '%{0}%'"

        conexao = FabricaDeConexoes.CriarConexao()
        dataSet = New DataSet()

        carregarClienteFisico()

    End Sub

    Private Sub carregarEstados()

        If (dataSet IsNot Nothing) Then
            dataAdapter = New OleDbDataAdapter(strQueryJuridico, conexao)
            dataAdapter.Fill(dataSet, "tblEstado")
        End If

    End Sub

    Private Sub carregarClienteJuridico()

        If (dataSet IsNot Nothing) Then
            dataAdapter = New OleDbDataAdapter(strQueryJuridico, conexao)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblCliente")

            dataView = New DataView(dataSet.Tables("tblCliente"), Nothing, "Código", DataViewRowState.CurrentRows)
            dataGridView.DataSource = dataView
        End If

    End Sub

    Private Sub carregarClienteFisico()

        If (dataSet IsNot Nothing) Then
            dataAdapter = New OleDbDataAdapter(strQueryFisico, conexao)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblCliente")

            dataView = New DataView(dataSet.Tables("tblCliente"), Nothing, "Código", DataViewRowState.CurrentRows)
            dataGridView.DataSource = dataView
        End If

    End Sub

    Private Sub btnFechar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFechar.Click
        Me.Dispose()
    End Sub

    Private Sub btnNovo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNovo.Click
        Try
            Dim frmcad As New CadastroCliente()
            frmcad.ShowDialog()
            carregarClienteFisico()

        Catch ex As Exception
            Debug.WriteLine(ex.StackTrace)
        End Try
    End Sub

    Private Sub cbFiltro_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbFiltro.SelectedIndexChanged

        txtFiltrar.Clear()
        txtFiltrar.Focus()

        If (cbFiltro.SelectedItem.ToString() = "CNPJ") Then

            carregarClienteJuridico()
            strTipoFiltro = "CNPJ like '{0}%'"

        Else
            carregarClienteFisico()

            Select Case (cbFiltro.SelectedItem.ToString())

                Case "Nome"
                    strTipoFiltro = "Nome like '%{0}%'"
                Case "CPF"
                    strTipoFiltro = "CPF like '{0}%'"
                Case "RG"
                    strTipoFiltro = "RG like '{0}%'"
                Case Else
                    strTipoFiltro = Nothing

            End Select
        End If
        
    End Sub

    Private Sub txtFiltrar_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFiltrar.TextChanged
        dataView.RowFilter = String.Format(strTipoFiltro, txtFiltrar.Text)
    End Sub

    Private Sub btnExcluir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcluir.Click

        Try
            Dim queryAuto As String = "DELETE * FROM tblAuto WHERE tblAuto.codCliente = " & dataGridView.CurrentRow.Cells(0).Value
            Dim queryTelefone As String = "DELETE * FROM tblTelefoneCliente WHERE tblTelefoneCliente.codCliente = " & dataGridView.CurrentRow.Cells(0).Value
            Dim queryCliente As String = "DELETE FROM tblCliente WHERE codCliente = " & dataGridView.CurrentRow.Cells.Item("Código").Value

            'Abre conexão e exclui os veículos do cliente
            command = New OleDbCommand(queryAuto, conexao)
            conexao.Open()
            command.ExecuteNonQuery()

            'Exclui os telefones do cliente
            command.CommandText = queryTelefone
            command.ExecuteNonQuery()

            'Exclui o registro do cliente
            command.CommandText = queryCliente
            command.ExecuteNonQuery()

            'Faz a atualização do DataSet (esvazia e preenche novamente)
            cmdBuilder = New OleDbCommandBuilder(dataAdapter)
            dataSet.Tables("tblCliente").Clear()
            dataAdapter.Update(dataSet, "tblCliente")

            'Atualiza o DataGridView
            dataView.Table = dataSet.Tables("tblCliente")
            dataGridView.DataSource = dataView

        Catch ex As OleDbException
            Debug.WriteLine("Erro: " & ex.Message)
        Finally
            command.Dispose()
            conexao.Close()
        End Try

    End Sub

    Private Sub btnEditar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditar.Click
        Dim frm As New CadastroCliente(dataGridView.CurrentRow.Cells.Item(0).Value)
        frm.ShowDialog()
        Dim cmdBuilder As New OleDbCommandBuilder(dataAdapter)
        dataAdapter.Update(dataSet, "tblCliente")
        carregarClienteFisico()
    End Sub
End Class